

ALTER    function fn_get_process_flow (@partNumber varchar(20))
returns  @@fn_result Table(ProcessStep_ID bigint, 
			PS_Type varchar(20), 
			Process_Type_Name varchar(20))

As
begin
	declare @@temp_processStepID bigint
	declare @@temp_ps_type varchar(20)
	declare @@temp_ps_type_name varchar(20)
	declare @@result Table(ProcessStep_ID bigint, 
			PS_Type varchar(20), 
			Process_Type_Name varchar(20))	
	
	select @@temp_processStepID =Process_Step.ProcessStep_ID, @@temp_ps_type=Process_Step.PS_Type, @@temp_ps_type_name=Process_Step.PS_Type_Name from Product, Process_Step where Product.P_Marketing_Part_Number=@partNumber and Product.ProcessStep_ID=Process_Step.ProcessStep_ID and Process_Step.PS_Type_Name = 'Finished Goods'
	--print 'ProcessStep_ID = '
	--print @@temp_processStepID
	--select @@temp_processStepID = 1106
	--select @@temp_ps_type=Process_Step.PS_Type, @@temp_ps_type_name=Process_Step.PS_Type_Name from Process_Step where Process_Step.ProcessStep_ID=@@temp_processStepID
	insert into @@result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@@temp_processStepID, @@temp_ps_type, @@temp_ps_type_name)
	
	declare @@temp bigint
	declare @@temp_id int
	select @@temp_id=AssemblyTrack_ID , @@temp=Mark_Pack_ProcessStep_ID from Mark_FG_Track_Table where FG_ProcessStep_ID=@@temp_processStepID
	
	select @@temp_processStepID = @@temp
	
	-- print 'Mark & Pack ProcessStepID = '
	-- print @@processStepID
	
	select @@temp_ps_type=Process_Step.PS_Type, @@temp_ps_type_name=Process_Step.PS_Type_Name from Process_Step where Process_Step.ProcessStep_ID=@@temp_processStepID
	insert into @@result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@@temp_processStepID, @@temp_ps_type, @@temp_ps_type_name)
	declare tempCursor CURSOR for
	select ProcessStep_ID from Test_Track_Table where AssemblyTrack_ID=@@temp_id order by ProcessStep_ID desc
	
	open tempCursor 
		fetch next from tempCursor 
		into @@temp
		while @@fetch_status = 0
		begin
	
			-- print 'TEST ProcessStepID = '
			-- print @@processStepID
			select @@temp_processStepID = @@temp
	
			select @@temp_ps_type=Process_Step.PS_Type, @@temp_ps_type_name=Process_Step.PS_Type_Name from Process_Step where Process_Step.ProcessStep_ID=@@temp_processStepID
			insert into @@result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@@temp_processStepID, @@temp_ps_type, @@temp_ps_type_name)
	
			fetch next from tempCursor
			into @@temp
		end
	
	close tempCursor
	deallocate tempCursor
	
	declare @@fab_track_id int
	select @@temp=ProcessStep_ID, @@fab_track_id=Track_ID from Assembly_Track_Table where AssemblyTrack_ID=@@temp_id
	
	select @@temp_processStepID = @@temp
	
	select @@temp_ps_type=Process_Step.PS_Type, @@temp_ps_type_name=Process_Step.PS_Type_Name from Process_Step where Process_Step.ProcessStep_ID=@@temp_processStepID
	insert into @@result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@@temp_processStepID, @@temp_ps_type, @@temp_ps_type_name)
	
	declare sortCursor CURSOR for
	select ProcessStep_ID from Sort_Track_Table where Track_ID=@@fab_track_id order by ProcessStep_ID desc
	
	open sortCursor
		fetch next from sortCursor
		into @@temp
		while @@fetch_status = 0
		begin
			select @@temp_processStepID = @@temp
	
			select @@temp_ps_type=Process_Step.PS_Type, @@temp_ps_type_name=Process_Step.PS_Type_Name from Process_Step where Process_Step.ProcessStep_ID=@@temp_processStepID
			insert into @@result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@@temp_processStepID, @@temp_ps_type, @@temp_ps_type_name)
	
			fetch next from sortCursor
			into @@temp
		end
		
	close sortCursor
	deallocate sortCursor
	
	select @@temp=ProcessStep_ID from Fab_Track_Table where Track_ID=@@fab_track_id
	
	select @@temp_processStepID = @@temp
	
	select @@temp_ps_type=Process_Step.PS_Type, @@temp_ps_type_name=Process_Step.PS_Type_Name from Process_Step where Process_Step.ProcessStep_ID=@@temp_processStepID
	insert into @@result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@@temp_processStepID, @@temp_ps_type, @@temp_ps_type_name)
	
	insert @@fn_result select *from @@result
	return
end 



